﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using classAbm;
using consultasSql.consultasVarias;

/// <summary>
/// Descripción breve de CSelectCable
/// </summary>

namespace consultasSql
{
    public class CConsultaCable : CDarDataSet
    {

        /*SELECT*/
        public DataSet select(bool deBaja, string mov , string desc, string ord)
        {
            
            string movex = "";

            if (!String.IsNullOrEmpty(mov.Trim()))
            {
                if (mov.Trim().Substring(0, 1) == "*")
                {
                    movex = " AND RTRIM(LTRIM(Movex)) LIKE '%' + @Movex";
                }
                else if (mov.Trim().Substring(mov.Trim().Length - 1, 1) == "*")
                {
                    movex = " AND RTRIM(LTRIM(Movex)) LIKE @Movex + '%'";
                }
                else
                {
                    movex = " AND RTRIM(LTRIM(Movex)) LIKE @Movex";
                }
            }

            string descripcion = "";

            if (!String.IsNullOrEmpty(desc.Trim()))
            {
                if (desc.Trim().Substring(0, 1) == "*")
                {
                    descripcion = " AND RTRIM(LTRIM(Descripcion)) LIKE '%' + @Descripcion";
                }
                else if (desc.Trim().Substring(desc.Trim().Length - 1, 1) == "*")
                {
                    descripcion = " AND RTRIM(LTRIM(Descripcion)) LIKE @Descripcion + '%'";
                }
                else
                {
                    descripcion = " AND RTRIM(LTRIM(Descripcion)) LIKE @Descripcion";
                }
            }


            string ordenar = "";

            if (!String.IsNullOrEmpty(ord.Trim()))
            {
                ordenar = " ORDER BY " + ord.Trim();
            }


            string consulta = "SELECT Id, Movex, Descripcion, CableMM, Voltage, TotalMts, DeBaja FROM Cable WHERE DeBaja = @deBaja" + movex + descripcion + ordenar;


            SqlCommand command = new SqlCommand();

            command.CommandType = CommandType.Text;
            command.CommandText = consulta;

            command.Parameters.Clear();

            command.Parameters.Add(new SqlParameter("@deBaja", SqlDbType.Int));
            command.Parameters["@deBaja"].Value = deBaja;

            if (!String.IsNullOrEmpty(mov.Trim()))
            {
                command.Parameters.AddWithValue("@Movex", mov.Trim().Replace("*", ""));
            }

            if (!String.IsNullOrEmpty(desc.Trim()))
            {
                command.Parameters.AddWithValue("@Descripcion", desc.Trim().Replace("*", ""));
            }

            return darDataSet(command);
        }


        /*UPDATE*/
        public bool update(CCable objeto)
        {
            bool bandera = false;

            string consulta = "UPDATE Cable SET Movex = @Movex, CableMM = @CableMM, Voltage = @Voltage, TotalMts = @TotalMts, Descripcion = @Descripcion, DeBaja = @DeBaja WHERE Id = @id";


            using (SqlConnection objConexion = new SqlConnection(CCadenaConexionSql.cadConexion()))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    if (objConexion.State == ConnectionState.Closed)
                    { objConexion.Open(); }

                    command.CommandType = CommandType.Text;
                    command.CommandText = consulta;

                    command.Parameters.Clear();

                    command.Parameters.AddWithValue("@Id", objeto.Id);
                    command.Parameters.AddWithValue("@Movex", objeto.Movex);
                    command.Parameters.AddWithValue("@Voltage", objeto.Voltage);

                    command.Parameters.Add(new SqlParameter("@CableMM", SqlDbType.Int));
                    command.Parameters["@CableMM"].Value = objeto.CableMM;


                    command.Parameters.Add(new SqlParameter("@TotalMts", SqlDbType.Decimal));
                    command.Parameters["@TotalMts"].Value = objeto.TotalMts;

                    command.Parameters.AddWithValue("@Descripcion", objeto.Descripcion);
                    command.Parameters.AddWithValue("@DeBaja", objeto.DeBaja);

                    command.Connection = objConexion;

                    try
                    {
                        command.ExecuteNonQuery();
                        bandera = true;
                    }
                    catch
                    {
                        bandera = false;
                    }

                    if (objConexion.State == ConnectionState.Open)
                    { objConexion.Close(); }
                }
            }
            return bandera;
        }


        /*INSERT*/
        public bool insert(CCable objeto)
        {
            bool bandera;

            string consulta = "INSERT INTO Cable (Movex, CableMM, Voltage, TotalMts, Descripcion, DeBaja) VALUES (@Movex, @CableMM, @Voltage, @TotalMts, @Descripcion, 0)";


            using (SqlConnection objConexion = new SqlConnection(CCadenaConexionSql.cadConexion()))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    if (objConexion.State == ConnectionState.Closed)
                    { objConexion.Open(); }

                    command.CommandType = CommandType.Text;
                    command.CommandText = consulta;

                    command.Parameters.Clear();

                    command.Parameters.AddWithValue("@Movex", objeto.Movex);
                    command.Parameters.AddWithValue("@Voltage", objeto.Voltage);

                    command.Parameters.Add(new SqlParameter("@CableMM", SqlDbType.Int));
                    command.Parameters["@CableMM"].Value = objeto.CableMM;


                    command.Parameters.Add(new SqlParameter("@TotalMts", SqlDbType.Decimal));
                    command.Parameters["@TotalMts"].Value = objeto.TotalMts;

                    command.Parameters.AddWithValue("@Descripcion", objeto.Descripcion);

                    command.Connection = objConexion;

                    try
                    {
                        command.ExecuteNonQuery();
                        bandera = true;
                    }
                    catch
                    {
                        bandera = false;
                    }

                    if (objConexion.State == ConnectionState.Open)
                    { objConexion.Close(); }
                }
            }
            return bandera;
        }

    }
}